package com.cn.admin.utils;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.DecimalFormat;
import java.util.*;

/**
 * 描述：
 *
 * @author chen_q_i@163.com
 * 2018/9/18 : 15:25.
 * @version : 1.0
 */
@Component
public class ExcelUtils {

    private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class);

     ExcelUtils() {
    }

    /**
     * 导出excel
     *
     * @return 消息实体
     */
    public void export(String[] titles, List<Map<String, Object>> datas, String[] properties,  String fileName) {

        SXSSFWorkbook wb=new SXSSFWorkbook(100);
        FileOutputStream output = null;
        ByteArrayOutputStream btyeOutput = null;

        // 创建工作薄

        // 创建工作表
        Sheet sheet=wb.createSheet("导出数据");
        // 设置默认列宽
        sheet.setDefaultColumnWidth(20);

        // 设置头部样式
        CellStyle cellTitleStyle = wb.createCellStyle();
        setDefaultTitleCellStyle(cellTitleStyle);
        // 设置头部字体
        Font font = wb.createFont();
        setDefaultTitleHSSFFont(font);
        cellTitleStyle.setFont(font);
        // 设置标题行
        Row titleRow = sheet.createRow(0);
        // 生成表格头部
        createTitlesRow(titles, cellTitleStyle, titleRow);

        // 设置样式
        CellStyle cellStyle = cellTitleStyle;
        setDefaultCellStyle(cellStyle);
        // 设置字体
        font = wb.createFont();
        setDefaultHSSFFont(font);
        cellStyle.setFont(font);
        // 生成表格数据
        createDataRows(wb, sheet, cellStyle, datas, properties);

        FileOutputStream fOut = null;//将数据写入Excel
        try {
            fOut = new FileOutputStream(fileName);
        } catch (FileNotFoundException e) {
            logger.error("file not fond exception:", e);
        }
        try {
            wb.write(fOut);
        } catch (IOException e) {
            logger.error("file write error:", e);
        } finally {
            try {
                fOut.flush(); // 刷新缓冲区
            } catch (IOException e) {
                logger.error("file flush error:", e);
            }
            try {
                fOut.close();
            } catch (IOException e) {
                logger.error("file close error:", e);
            }
        }
    }


      /**
     * 导出excel
     *
     * @return 消息实体
     */
    public SXSSFWorkbook exportWB(String[] titles, String[] properties,  String fileName) {
        logger.info("{}开始导出",fileName);

        SXSSFWorkbook  wb=new SXSSFWorkbook(5000);
        FileOutputStream output = null;
        ByteArrayOutputStream btyeOutput = null;

        // 创建工作薄

        // 创建工作表
        Sheet sheet=wb.createSheet("导出数据");
        // 设置默认列宽
        sheet.setDefaultColumnWidth(20);

        // 设置头部样式
        CellStyle cellTitleStyle = wb.createCellStyle();
        setDefaultTitleCellStyle(cellTitleStyle);
        // 设置头部字体
        Font font = wb.createFont();
        setDefaultTitleHSSFFont(font);
        cellTitleStyle.setFont(font);
        // 设置标题行
        Row titleRow = sheet.createRow(0);
        // 生成表格头部
        createTitlesRow(titles, cellTitleStyle, titleRow);

        // 设置样式
        CellStyle cellStyle = cellTitleStyle;
        setDefaultCellStyle(cellStyle);
        // 设置字体
        font = wb.createFont();
        setDefaultHSSFFont(font);
        cellStyle.setFont(font);
//        logger.info("{} 开始写入数据，共{}条",fileName,datas.size());
        // 生成表格数据
//        createDataRows(wb, sheet, cellStyle, datas, properties);
        logger.info("{} 创建完成完成",fileName);
      return  wb;
    }




  /**
     * 导出excel
     *
     * @return 消息实体
 * @throws Exception
     */
    public SXSSFWorkbook exportWB(String[] titles,List datas, String[] properties, String fileName) throws Exception {
        logger.info("{}开始导出",fileName);

        SXSSFWorkbook  wb=new SXSSFWorkbook(5000);
        FileOutputStream output = null;
        ByteArrayOutputStream btyeOutput = null;

        // 创建工作薄

        // 创建工作表
        Sheet sheet=wb.createSheet("导出数据");
        // 设置默认列宽
        sheet.setDefaultColumnWidth(20);

        // 设置头部样式
        CellStyle cellTitleStyle = wb.createCellStyle();
        setDefaultTitleCellStyle(cellTitleStyle);
        // 设置头部字体
        Font font = wb.createFont();
        setDefaultTitleHSSFFont(font);
        cellTitleStyle.setFont(font);
        // 设置标题行
        Row titleRow = sheet.createRow(0);
        // 生成表格头部
        createTitlesRow(titles, cellTitleStyle, titleRow);

        // 设置样式
        CellStyle cellStyle = cellTitleStyle;
        setDefaultCellStyle(cellStyle);
        // 设置字体
        font = wb.createFont();
        setDefaultHSSFFont(font);
        cellStyle.setFont(font);
//        logger.info("{} 开始写入数据，共{}条",fileName,datas.size());
        // 生成表格数据
        createDataRows(wb, sheet, cellStyle, datas, properties);
        logger.info("{} 创建完成完成",fileName);
      return  wb;
    }

    /**
     * 生成数据
     *
     * @param datas      数据源
     * @param wb         工作薄
     * @param sheet      sheet工作表
     * @param cellStyle  样式
     * @param properties 列名
     */
    @SuppressWarnings("unchecked")
    private static void createDataRows(Workbook wb, Sheet sheet, CellStyle cellStyle, Collection<?> datas,
                                       String[] properties) {
        List<Map<String, Object>> ldatas = (List<Map<String, Object>>) datas;
        for (int i = 0; i < ldatas.size(); i++) {
            Row row = sheet.createRow(i + 1);
            for (int j = 0; j < properties.length; j++) {
                // 创建单元格
                Cell cell = row.createCell(j);
                // 设置样式
                // cell.setCellStyle(cellStyle);
                Object value = ldatas.get(i).get(properties[j]);
                // 二进制数组数据表示图片
                cell.setCellValue(value == null ? "" : String.valueOf(value));
            }
        }
    }

    /**
     * 创建标题列
     *
     * @param titles    标题集合
     * @param cellStyle 列样式
     * @param titleRow  表格行
     */
    private static void createTitlesRow(String[] titles, CellStyle cellStyle, Row titleRow) {
        for (int i = 0; i < titles.length; i++) {
            Cell cell = titleRow.createCell(i);
            cell.setCellValue(new HSSFRichTextString(titles[i]));
            cell.setCellStyle(cellStyle);
        }
    }

    /**
     * 设置默认字体样式
     *
     * @param font
     */
    private static void setDefaultHSSFFont(Font font) {
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
    }

    /**
     * 设置默认头部字体样式
     *
     * @param font
     */
    private static void setDefaultTitleHSSFFont(Font font) {
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    }

    /**
     * 设置表格默认样式
     *
     * @param
     */
    private static void setDefaultCellStyle(CellStyle cellStyle) {
        cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

    }

    /**
     * 设置表格头部默认样式
     *
     * @param cellStyle 表格样式对象
     */
    private static void setDefaultTitleCellStyle(CellStyle cellStyle) {
        setDefaultCellStyle(cellStyle);
        cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    }


    /**
     * 设置输出格式
     *
     * @param response
     * @param filename 输出的文件名称
     */
    public static void setResponse(HttpServletResponse response, String filename) {
    	response.setHeader("content-type", "application/vnd..ms-excel");
        //response.setContentType("application/vnd..ms-excel");
        response.addHeader("Content-Disposition", "attachment;filename=" + filename);
    }

    /**
     * 根据图片路径生成图片二进制数据
     *
     * @param imgPath 图片真实路径
     * @return 二进制图片数据
     * @throws Exception
     */
    public static byte[] getImgBytesByPath(String imgPath) throws Exception {
        if (StringUtils.isBlank(imgPath))
            return null;
        File file = new File(imgPath);
        if (!file.exists())
            return null;
        BufferedInputStream bis = new BufferedInputStream(new FileInputStream(file));
        if (bis != null) {
            byte[] bytes = new byte[bis.available()];
            while (bis.read(bytes) != -1) {
                return bytes;
            }
            bis.close();
        }
        return null;
    }

    public static List<List<String>> readExcel(String path) throws Exception {
        File file = new File(path);
        String extentionNm = file.getName().substring(file.getName().lastIndexOf(".") + 1);
        if ("xls".equals(extentionNm)) {
            //return readXls(file);
        } else if ("xlsx".equals(extentionNm)) {
            return readXlsx(file);
        }
        return null;
    }

    /**
     * 读取xls
     *
     * @return
     * @throws Exception
     */
    public static List<List<String>> readXls(MultipartFile file) throws Exception {
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));

        List<List<String>> result = new ArrayList<List<String>>();
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }

            //从第几行开始保存列表
            for (int numRow = 1; numRow <= hssfSheet.getLastRowNum(); numRow++) {
                HSSFRow hssfRow = hssfSheet.getRow(numRow);
                if (hssfRow == null) {
                    continue;
                }
                // int minColIx = hssfRow.getFirstCellNum();
                int maxColIx = hssfRow.getLastCellNum();
                logger.info("表格列数 maxColIx={}",maxColIx);
                List<String> rowList = new ArrayList<String>();
                for (int colIx = 0; colIx < maxColIx; colIx++) {
                    HSSFCell cell = hssfRow.getCell(colIx);
                    if (cell == null)
                        rowList.add(null);
                    else
                        rowList.add(ExcelUtils.getStringVal(cell));
                }
                result.add(rowList);
            }
        }

        return result;
    }

    /**
     * 读取xlsx
     *
     * @return
     * @throws Exception
     */
    public static List<List<String>> readXlsx(File file) throws Exception {
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(file));

        List<List<String>> result = new ArrayList<List<String>>();
        for (XSSFSheet xssfSheet : xssfWorkbook) {
            if (xssfSheet == null) {
                continue;
            }
            for (int numRow = 1; numRow <= xssfSheet.getLastRowNum(); numRow++) {
                XSSFRow xssfRow = xssfSheet.getRow(numRow);
                if (xssfRow == null) {
                    continue;
                }
                // int minColIx = hssfRow.getFirstCellNum();
                int maxColIx = xssfRow.getLastCellNum();
                List<String> rowList = new ArrayList<String>();
                for (int colIx = 0; colIx < maxColIx; colIx++) {
                    XSSFCell cell = xssfRow.getCell(colIx);
                    if (cell == null) {
                        continue;
                    }
                    rowList.add(ExcelUtils.getStringVal(cell));
                }
                result.add(rowList);
            }
        }

        return result;
    }

    /**
     * @param cell
     * @return
     */
    private static String getStringVal(XSSFCell cell) {
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
            case Cell.CELL_TYPE_FORMULA:
                return cell.getCellFormula();
            case Cell.CELL_TYPE_NUMERIC:
                cell.setCellType(Cell.CELL_TYPE_STRING);
                return cell.getStringCellValue();
            case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            default:
                return "";
        }
    }

    /**
     * @param cell
     * @return
     */
    private static String getStringVal(HSSFCell cell) {
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
            case Cell.CELL_TYPE_FORMULA:
                return cell.getCellFormula();
            case Cell.CELL_TYPE_NUMERIC:
                cell.setCellType(Cell.CELL_TYPE_STRING);
                return cell.getStringCellValue();
            case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            default:
                return "";
        }
    }



    public static Map<Long,String> importExcel(InputStream inputStream) throws Exception {
        XSSFWorkbook book = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = book.getSheetAt(0);
        XSSFRow row = null;
        Map<Long, String> map = new HashMap<>();
        for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
//            MessageCustomer customer = new MessageCustomer();
            row = sheet.getRow(rowNum);
            String cell0 = null;
            try {
                cell0 = ExcelUtils.getValue(row.getCell(0)).trim();
            } catch (Exception e) {
                throw new Exception(e.getMessage()+"解析用户id异常");
            }
            if (StringUtils.isNotBlank(cell0)) {
                map.put(Long.valueOf(cell0), null);
            }
            String cell1 = null;
            try {
                cell1 = ExcelUtils.getValue(row.getCell(1)).trim();
            } catch (Exception e) {
                throw new Exception(e.getMessage()+"解析用户手机号异常");
            }
            if (StringUtils.isNotBlank(cell0) && StringUtils.isNotBlank(cell1) && cell1.length() == 11) {
                map.put(Long.valueOf(cell0), cell1);
            }

        }
        return map;
    }

    public static String getValue(XSSFCell cell) {
        if (cell == null) {
            return "";
        }
        String cellValue = "";
        switch (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_NUMERIC: // 数字
                DecimalFormat df = new DecimalFormat("0.##");
                cellValue = df.format(cell.getNumericCellValue());
                break;

            case XSSFCell.CELL_TYPE_STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;

            case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue() + "";
                break;

            case XSSFCell.CELL_TYPE_FORMULA: // 公式
                cellValue = cell.getCellFormula() + "";
                break;

            case XSSFCell.CELL_TYPE_BLANK: // 空值
                cellValue = "";
                break;

            case XSSFCell.CELL_TYPE_ERROR: // 故障
                cellValue = "非法字符";
                break;

            default:
                cellValue = "未知类型";
                break;
        }

        return cellValue;
    }



}
